using ReflectionStudio.Core.Events;
using System;
using System.Data;
using System.Data.OracleClient;
namespace WPFExcelReport
{
   
    public partial class VICC_HRSTAMM
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                default:
                    #region====ICC_HRSTAMM======
                    dwReturn["ROWNID"] = "0";
                    dwReturn["MANDANT"] = "00";
                    dwReturn["COMPANY"] = "   ";
                    dwReturn["USERID"] = "        ";
                    dwReturn["PWD"] = "           ";
                    dwReturn["EXCELFILE"] = "   ";
                    dwReturn["DBSYSTEM"] = "intex1";
                    dwReturn["DBUSER"] = "intex";
                    dwReturn["DBPWD"] = "intex";
                    dwReturn["SQLDB"] = "Data Source=192.168.18.227;Initial Catalog=jinling;Persist Security Info=True;User ID=sa;   ";
                    dwReturn["SQLDBUSER"] = "sa  ";
                    dwReturn["SQLDBPWD"] = "sasa  ";
                    dwReturn["NETDRIVE"] = "   ";
                    dwReturn["ROOTDRIVE"] = "   ";
                    dwReturn["BYAGENT"] = "YES";
                    dwReturn["UPDATER"] = " ";
                    dwReturn["IPADDRESS"] = "192.168.1.11";
                    dwReturn["IPADDRESS2"] = "80.166.130.110";
                    dwReturn["NEWUSER"] = " ";
                    dwReturn["NEWTIME"] = "";
                    dwReturn["CHGUSER"] = " ";
                    dwReturn["CHGTIME"] = "0";
                    #endregion
                    break;
            }
        }

        public void InsertICC_HRSTAMM(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {

            dwData["NEWUSER"] = app.User;
            dwData["NEWTIME"] = date;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                default:
                case 1:
                    or1.CommandText = "INSERT INTO ICC_HRSTAMM ( MANDANT,ROWNID,COMPANY,USERID,PWD,EXCELFILE,DBSYSTEM,DBUSER,DBPWD,SQLDB,SQLDBUSER,SQLDBPWD,NETDRIVE,ROOTDRIVE,CHECKFOLDER,BYAGENT,UPDATER,IPADDRESS,IPADDRESS2,NEWUSER,NEWTIME,CHGUSER,CHGTIME)"
                    + " VALUES (:MANDANT:,:ROWNID:,:COMPANY:,:USERID:,:PWD:,:EXCELFILE:,:DBSYSTEM:,:DBUSER:,:DBPWD:,:SQLDB:,:SQLDBUSER:,:SQLDBPWD:,:NETDRIVE:,:ROOTDRIVE:,:CHECKFOLDER:,:BYAGENT:,:UPDATER:,:IPADDRESS:,:IPADDRESS2:,:NEWUSER:,:NEWTIME:,:CHGUSER:,:CHGTIME:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }

        public DataSet QueryICC_HRSTAMM(DataRow dwData, int option = 1)
        { 
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1: SResult = QIHR01(dwData); break;
                case 2: SResult = QIHR02(dwData); break;
                case 3: SResult = QIHR03(dwData); break; 
                case 9999: SResult = QIHR99(dwData);  break;
                case -1: SResult = QIHR_1(dwData); break; 
            } 
            return SResult;
        }
        public DataSet QIHR01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM ICC_HRSTAMM WHERE  ROWNID=:ROWNID:"           
            + " ORDER BY USERID";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        
        public DataSet QIHR02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM ICC_HRSTAMM WHERE  MANDANT=:MANDANT: AND COMPANY=:COMPANY: AND RTRIM(USERID)=:USERID: "      
            + " ORDER BY USERID";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QIHR03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM ICC_HRSTAMM WHERE  MANDANT=:MANDANT:  AND RTRIM(USERID)=:USERID: AND RTRIM(PWD)=:PWD:"; ;
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QIHR_1(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*,P.* FROM ICC_HRSTAMM M "
                + " LEFT JOIN HRSTAMM P ON MANDANT=HRMANDANT AND  HRBENID=USERID"
                   + " ORDER BY USERID";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QIHR99(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT  DISTINCT USERID,USERID AS KEY, USERID AS DES FROM ICC_HRSTAMM "
            +" ORDER BY USERID";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void DeleteICC_HRSTAMM(DataRow dwData, int option = 1)
        {
            try
            {
                or1 = OracleDB.CreateCommand();
                switch (option)
                {

                    case 0:
                        or1.CommandText = "DELETE FROM ICC_HRSTAMM "; 
                        break;
                    case 1:
                        or1.CommandText = "DELETE FROM ICC_HRSTAMM WHERE  ROWNID=:ROWNID:"; ;
                        or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                        break;
                }
                Connect2Proxy(or1);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void UpdateICC_HRSTAMM(DataRow dwData, int option = 1)
        {
            if (dwData != null)
            {
                dwData["CHGUSER"] = app.User;
                dwData["CHGTIME"] = date;
            }

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE ICC_HRSTAMM SET MANDANT=:MANDANT:,ROWNID=:ROWNID:,COMPANY=:COMPANY:,USERID=:USERID:,PWD=:PWD:,EXCELFILE=:EXCELFILE:,DBSYSTEM=:DBSYSTEM:,DBUSER=:DBUSER:,DBPWD=:DBPWD:,SQLDB=:SQLDB:,SQLDBUSER=:SQLDBUSER:,SQLDBPWD=:SQLDBPWD:,NETDRIVE=:NETDRIVE:,ROOTDRIVE=:ROOTDRIVE:,CHECKFOLDER=:CHECKFOLDER:,BYAGENT=:BYAGENT:,UPDATER=:UPDATER:,IPADDRESS=:IPADDRESS:,IPADDRESS2=:IPADDRESS2:,NEWUSER=:NEWUSER:,NEWTIME=:NEWTIME:,CHGUSER=:CHGUSER:,CHGTIME=:CHGTIME:"
                + " WHERE  ROWNID=:ROWNID:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
    }
}
